package com.siqi.hr.system.custom.dao.mysql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.siqi.hr.system.common.authority.Sysdata;
import com.siqi.hr.system.common.enums.OperationEnum;
import com.siqi.hr.system.common.model.filter.QueryDataEntity;
import com.siqi.hr.system.common.sql.STable;
import com.siqi.hr.system.custom.dao.intf.FieldRecordDao;
import com.siqi.hr.system.custom.dao.intf.SelectBoxDao;
import com.siqi.hr.system.custom.entity.EntityOperation;
import com.siqi.hr.system.custom.entity.EntityRecord;
import com.siqi.hr.system.custom.entity.FieldRecord;
import com.siqi.hr.system.custom.entity.SelectBox;
import com.siqi.hr.system.organization.entity.FieldControled;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import org.springframework.util.Assert;

@Component("fieldRecordDao")
public class FieldRecordDaoImpl implements FieldRecordDao {
    private static final Logger logger = Logger.getLogger(FieldRecordDaoImpl.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private SelectBoxDao selectBoxDao;

    @Override
    public FieldRecord getFieldInfo(final int fieldId) {
        String sql = "select id,name,entity_id,inchinese from sys_custom_field where id = ? ";
        try {
            return (FieldRecord) this.jdbcTemplate.queryForObject(sql,new Object[] { Integer.valueOf(fieldId) },new RowMapper() {
                public FieldRecord mapRow(ResultSet rs,int rowNum) throws SQLException {
                    FieldRecord fr = new FieldRecord();
                    fr.setId(fieldId);
                    fr.setName(rs.getString("name"));
                    fr.setEntityRecord(new EntityRecord(Integer.valueOf(rs.getInt("entity_id"))));
                    fr.setInchinese(rs.getString("inchinese"));
                    return fr;
                }
            });
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Map<String, Object>> getFieldRecords(QueryDataEntity queryData, Boolean sysField) {
        StringBuffer sql = new StringBuffer("SELECT f.id, f.`name`,f.position, f.inchinese , f.calfield , f.batch_update,f.nulldisable,f.length,");
        sql.append("f.precisions,f.value_unique,f.sys_field ,f.share_field_id,f.controled,  CONCAT(e.inchinese,':',sf.inchinese) as enum_share,");
        sql.append("f.restricter  from  sys_custom_field f   left join sys_custom_field sf on f.share_field_id= sf.id ");
        sql.append(" left join sys_custom_entity e on sf.entity_id = e.table_code  where f.entity_id = ?  and f.sys_field = ? ");
        sql.append(" and f.name <> 'id'    and f.name <> 'flag_bit' and f.name <> 'valid_data_bit' ");
        sql.append(" and f.name <> 'dept_rank_varchar' and f.name <> 'approval_state_varchar' order by f.position asc ");
        if(queryData.getLimit() > 0){
            sql.append(" limit ").append(queryData.getOffset()).append(",").append(queryData.getLimit());
        }
        return jdbcTemplate.queryForList(sql.toString(),new Object[] { queryData.getTableCode(), sysField });
    }

    @Override
    public int getCountFieldRecords(int entityId, Boolean sysField) {
        StringBuffer sql = new StringBuffer("select count(f.id) from sys_custom_field f   left join sys_custom_field sf on f.share_field_id= sf.id ");
        sql.append(" left join sys_custom_entity e on sf.entity_id = e.table_code  where f.entity_id = ?  and f.sys_field = ? and f.name <> 'id' ");
        sql.append(" and f.name <> 'flag_bit' and f.name <> 'valid_data_bit'  and f.name <> 'dept_rank_varchar' and f.name <> 'approval_state_varchar' ");
        return jdbcTemplate.queryForObject(sql.toString(),new Object[] { entityId, sysField },Integer.class);
    }

    @Override
    public List<FieldRecord> getFieldRestrictedList(Integer entityId) {
        List<FieldRecord> fieldList = new ArrayList<FieldRecord>();
        if(entityId != null && entityId != 0){
            String sql = " select id, name, inchinese from sys_custom_field where entity_id = ? ";
            try {
                fieldList = jdbcTemplate.query(sql, new Object[] { entityId },new RowMapper() {
                    public FieldRecord mapRow(ResultSet rs, int rowNum) throws SQLException {
                        FieldRecord field = new FieldRecord();
                        field.setId(rs.getInt("id"));
                        field.setName(rs.getString("name"));
                        field.setInchinese(rs.getString("inchinese"));
                        return field;
                    }
                });
            } catch (DataAccessException e) {
                e.printStackTrace();
            }
        }
        return fieldList;
    }

    @Override
    public List<FieldRecord> getAllFields(int entityId) {
        StringBuffer sql = new StringBuffer(" SELECT id,name,inchinese,entity_id,sys_field FROM sys_custom_field WHERE entity_id = ? and name <> 'id' ");
        sql.append(" and name <> 'flag_bit' and name <> 'valid_data_bit' and name <> 'dept_rank_varchar' and name <> 'approval_state_varchar' order by position asc");
        List<FieldRecord> fieldList = this.jdbcTemplate.query(sql.toString(), new RowMapper() {
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            FieldRecord field = new FieldRecord();
            field.setId(rs.getInt("id"));
            field.setName(rs.getString("name"));
            field.setInchinese(rs.getString("inchinese"));
            field.setEntityRecord(new EntityRecord(rs.getInt("entity_id")));
            field.setSysField(rs.getBoolean("sys_field"));
            return field;
            }
        }, new Object[] { entityId});
        return fieldList;
    }

    @Override
    public int fieldSortUpdate(String sortIds) {
        int [] nums = new int [0];
        String [] ids = sortIds.split(",");
        if(ids.length > 0){
            String [] sqls = new String[ids.length];
            for (int i = 0; i < ids.length; i++) {
                String [] strs = ids[i].split("_");
                sqls [i] = "update sys_custom_field set position = "+strs[1]+" where id = "+strs[0];
            }
            if(sqls.length > 0){
                nums = jdbcTemplate.batchUpdate(sqls);
            }
        }
        return nums.length;
    }

    @Override
    public List<FieldRecord> getAllBoxFields() {
        StringBuffer sql = new StringBuffer("select f.id, f.inchinese, f.name , e.inchinese ech from sys_custom_field f,");
        sql.append(" sys_custom_entity e where  f.entity_id = e.table_code and  (f.name like '%_singlebox' or f.name like '%_mutibox' ");
        sql.append(" or f.name like '%_radio')  and f.share_field_id is null ");

        return jdbcTemplate.query(sql.toString(),new RowMapper() {
            public Object mapRow(ResultSet rs, int arg1) throws SQLException {
                FieldRecord fr = new FieldRecord();
                fr.setId(rs.getInt("id"));
                fr.setInchinese(rs.getString("ech") + ":" + rs.getString("inchinese"));
                return fr;
            }
        });
    }

    @Override
    public boolean isFieldNameExist(Integer entityId, String fieldName) {
        String sql = "select count(*) from sys_custom_field where entity_id = ? and name = ? ";
        int i = jdbcTemplate.queryForObject(sql,new Object[] { entityId, fieldName },Integer.class);
        if (i > 0){
            return true;
        }
        return false;
    }

    @Override
    public List<FieldRecord> getFieldsWitchQuoteEntity(int tableCode, int quoteEntityId) {
        String sql = "select id,name from sys_custom_field where entity_id = ? and name like 'pk_t_" + quoteEntityId + "_%'";
        try {
            return this.jdbcTemplate.query(sql, new RowMapper() {
                public Object mapRow(ResultSet rs, int arg1) throws SQLException {
                    FieldRecord field = new FieldRecord();
                    field.setId(rs.getInt("id"));
                    field.setName(rs.getString("name"));
                    return field;
                }
            }, new Object[] { tableCode});
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return new ArrayList();
    }

    @Override
    public FieldRecord getFieldRecord(Integer fieldId) {
        StringBuffer sql = new StringBuffer("SELECT f.id, f.`name`,f.inchinese,f.entity_id,e.system_entity,e.define_type,f.list_show,f.filter_show,f.hidden_edit,");
        sql.append("f.hidden_show,f.can_order,f.quote_entity_id,f.default_value,f.nulldisable,f.length,f.precisions,f.calfield,f.value_unique,f.case_sensitive,");
        sql.append("f.first_as_default,f.edit_row_count,f.date_type,f.read_only,f.batch_update,f.share_field_id,f.related_hidden,f.controled,f.restricter,");
        sql.append("f.ifcascade,f.sys_field,f.comment FROM sys_custom_field AS f,sys_custom_entity e WHERE f.entity_id = e.table_code and f.id = ?");
        return jdbcTemplate.queryForObject(sql.toString(),new FieldRecord.getFieldAll(),new Object[] { fieldId });
    }

    @Override
    public int updateFieldRecord(FieldRecord fieldRecord) {
        StringBuffer sql = new StringBuffer("update sys_custom_field set inchinese=? ,comment = ?, default_value= ?,nulldisable= ?,length= ?,");
        sql.append("precisions= ?,  calfield= ?,  read_only= ?,can_order= ?,value_unique= ?,case_sensitive= ?,first_as_default= ?,edit_row_count= ?,");
        sql.append("date_type= ?,batch_update= ?,controled= ? where id = ?");

        int counts = jdbcTemplate.update(sql.toString(), new Object[]{
            fieldRecord.getInchinese(),
            fieldRecord.getComment(),
            fieldRecord.getDefaultValue(),
            fieldRecord.isNulldisable(),
            fieldRecord.getLength(),
            fieldRecord.getPrecision(),
            fieldRecord.isCalField(),
            fieldRecord.isReadOnly(),
            fieldRecord.isCanOrder(),
            fieldRecord.isValueUnique(),
            fieldRecord.isCaseSensitive(),
            fieldRecord.isFirstAsDefault(),
            fieldRecord.getEditRowCount(),
            fieldRecord.getDateType(),
            fieldRecord.isBatchUpdate(),
            fieldRecord.isControled(),
            fieldRecord.getId()
        });
        return counts;
    }

    @Override
    public void updateFieldColumnStruct(FieldRecord fnew) {
        int entityId = fnew.getEntityRecord().getTableCode();
        String fieldName = fnew.getName();

        int length = fnew.getLength();
        int precision = fnew.getPrecision();
        String sql = "ALTER TABLE `t_" + entityId + "` MODIFY COLUMN `" + fieldName + "` ";
        if ((fieldName.endsWith("_decimal")) || (fieldName.endsWith("_precent")) || (fieldName.endsWith("_currency"))) {
            sql = sql + "decimal(" + length + "," + precision + ")";
        } else if (fieldName.endsWith("_varchar"))
            sql = sql + "varchar(" + length + ")";
        else if (fieldName.endsWith("_email") || fieldName.endsWith("_web") || fieldName.endsWith("_email") || fieldName.endsWith("_autosno") || fieldName.endsWith("_img") || fieldName.endsWith("_link"))
            sql = sql + " varchar(64) ";
        else if (fieldName.endsWith("_phone") || fieldName.startsWith("pk_"))
            sql = sql + " varchar(20) ";
        else if (fieldName.endsWith("_radio") || fieldName.endsWith("_singlebox") || fieldName.endsWith("_mutibox")) {
            sql = sql + " int ";
        } else if (fieldName.endsWith("_datetime")) {
            sql = sql + " datetime ";
        } else if (fieldName.endsWith("_date")) {
            sql = sql + " date ";
        } else if (fieldName.endsWith("_textarea")) {
            sql = sql + " text ";
        } else if (fieldName.endsWith("_textblock")) {
            sql = sql + " mediumtext ";
        } else if (fieldName.endsWith("_int"))
            sql = sql + " int(" + length + ") ";
        else {
            sql = sql + " bit(1) ";
        }
        if (fnew.getDefaultValue() != null && !fnew.getDefaultValue().equals(""))
            sql = sql + "  DEFAULT " + fnew.getDefaultValue();
        else if (fieldName.endsWith("_checkbox")) {
            sql = sql + "  DEFAULT 0";
        }
        logger.info("sql===="+sql);
        try {
            this.jdbcTemplate.update(sql);
        } catch (Exception e) {
        }
    }

    @Override
    public boolean checkedFieldIsExist(String fieldName, int tableCode) {
        String sql0 = "select count(0) from information_schema.`COLUMNS`  where TABLE_SCHEMA = ? and TABLE_NAME = ? and COLUMN_NAME = ? ";
        int columnlen = jdbcTemplate.queryForObject(sql0,new Object[] {Sysdata.getInstance().getDatabase(),"t_" + tableCode, fieldName },Integer.class);
        Boolean existBoo = columnlen > 0;
        //如果当前要删除的字段在mysql数据库中没有注册则删除该字段
        if (!existBoo) {
            delRegFieldByTableCode(tableCode,fieldName);
        }
        return existBoo;
    }

    @Override
    public boolean checkedFieldHasData(String fieldName, int tableCode) {
        int i = 0;
        try {
            String sql = "select count(0) from " + STable.TABLE_HEAD + tableCode + " where " + fieldName + " is not null and " + fieldName + " <> '' ";
            i = this.jdbcTemplate.queryForObject(sql,Integer.class);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return i > 0;
    }

    @Override
    public void delRegFieldByTableCode(int tableCode, String fieldName) {
        StringBuffer sql = new StringBuffer("delete from sys_custom_field where entity_id = " + tableCode + " and name = '" + fieldName + "' ");
        jdbcTemplate.execute(sql.toString());
    }

    @Override
    public Map<String, FieldControled> getFieldControledList(Integer entityId, Integer fieldId) {
        Assert.notNull(entityId);
        Assert.notNull(fieldId);

        final Map<String,FieldControled> map = new HashMap<String,FieldControled>();

        String sql = "SELECT * FROM sys_org_role_field where entity_id = ? and field_id = ? ";
        jdbcTemplate.query(sql,new Object[] { entityId, fieldId }, new RowMapper() {
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                FieldControled fc = new FieldControled();
                fc.setFieldName(rs.getString("field_name"));
                fc.setForbidEditCharge(rs.getBoolean("forbid_edit_charge"));
                fc.setForbidViewCharge(rs.getBoolean("forbid_view_charge"));
                map.put(rs.getString("role_id"), fc);
                return null;
            }
        });
        return map;
    }

    @Override
    public void saveFieldControled(Integer entityId, Integer fieldId, String fieldName, String[] authArr) {
        Assert.notNull(entityId);
        Assert.notNull(fieldId);
        Assert.notEmpty(authArr);
        List<String> sqlList = new ArrayList<String>();
        for (int i = 0; i < authArr.length; i++) {
            String[] rowAuth = authArr[i].split("\\|");
            StringBuffer delSql = new StringBuffer(" DELETE FROM sys_org_role_field WHERE entity_id = " + entityId + " and field_id = " + fieldId + " and role_id = '" + rowAuth[0] + "' ");
            sqlList.add(delSql.toString());
            StringBuffer insSql = new StringBuffer(" INSERT INTO sys_org_role_field(role_id,entity_id,field_id,field_name, forbid_edit_charge,forbid_view_charge)  VALUES( '");
            insSql.append(rowAuth[0]+"',"+entityId+","+fieldId+",'"+fieldName+"',"+rowAuth[1]+","+rowAuth[2]+")");
            sqlList.add(insSql.toString());
        }

        if (sqlList.size() > 0){
            jdbcTemplate.batchUpdate(sqlList.toArray(new String[sqlList.size()]));
        }
    }

    @Override
    public List<FieldRecord> getFieldsByEntityId(int entityId, int fieldId) {
        StringBuffer sql = new StringBuffer("select id,name,inchinese from sys_custom_field where entity_id = ? and name like '%_singlebox' and id <> ?");
        List<FieldRecord> fields = jdbcTemplate.query(sql.toString(),new FieldRecord.MainFieldRecord(), new Object[] {entityId , fieldId});
        return fields;
    }

    @Override
    public Integer getFieldRestricter(Integer fieldId) {
        Assert.notNull(fieldId);

        if (fieldId != 0) {
            try {
                String sql = " SELECT restricter FROM sys_custom_field WHERE id = ? ";
                return jdbcTemplate.queryForObject(sql,new Object[] { fieldId },Integer.class);
            } catch (DataAccessException e) {
                e.printStackTrace();
            }
        }

        return null;
    }

    @Override
    public int saveFieldRestricted(Integer fieldId, Integer resFieldId, Integer pidresFieldId) {
        Assert.notNull(fieldId);
        Assert.notNull(resFieldId);
        Assert.notNull(pidresFieldId);

        StringBuilder sql1 = new StringBuilder(" UPDATE sys_custom_field SET ");
        StringBuilder sql2 = new StringBuilder(" UPDATE sys_custom_field SET ");
        if (resFieldId == 0) {
            sql1.append(" restricter = null ");
            sql2.append(" ifcascade = null ");
        } else {
            sql1.append(" restricter = " + resFieldId);
            sql2.append(" ifcascade = " + fieldId);
        }
        sql1.append(" WHERE id =  " + fieldId);

        if (resFieldId == 0) {
            sql2.append(" where id = " + pidresFieldId);
        }else {
            sql2.append(" where id = " + resFieldId);
        }

        String[] sqls = { sql1.toString(), sql2.toString() };

        int [] nums = jdbcTemplate.batchUpdate(sqls);
        return nums.length;
    }

    @Override
    public List<Map<String, Object>> queryRestrictFields(Integer fieldId, Integer resFieldId) {
        Assert.notNull(fieldId);
        Assert.notNull(resFieldId);

        String resSql = " SELECT * FROM sys_custom_field_restrict WHERE field_id = ? and res_field_id = ? ";
        List<Map<String, Object>> resList = jdbcTemplate.queryForList(resSql, new Object[] {fieldId, resFieldId });

        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();

        List<SelectBox> resFieldList = selectBoxDao.getAllSelectBoxByFieldId(resFieldId,"");
        for (SelectBox sel : resFieldList) {
            Map<String,Object> map = new HashMap<String,Object>();
            map.put("id", sel.getId());
            List<EntityOperation> optionList = new ArrayList<EntityOperation>();
            //所有实体默认都有编辑按钮
            EntityOperation editField = new EntityOperation("编辑","editRestrictField","FieldRecord/fieldRestrictedEdit.do","glyphicon glyphicon-edit", OperationEnum.showColumn.name(),new EntityRecord(0));
            optionList.add(editField);
            map.put("operation", optionList);
            map.put("resFieldName", sel.getName());
            Integer selid = sel.getId();
            StringBuffer options = new StringBuffer();
            for (Map<String, Object> resmap : resList) {
                if (resmap.get("res_option_id").equals(selid)) {
                    options.append(resmap.get("option_name") + ",");
                }
            }
            if (options.indexOf(",") > 0) {
                options = options.deleteCharAt(options.lastIndexOf(","));
            }
            map.put("fieldName", options);
            list.add(map);
        }
        return list;
    }

    @Override
    public List<SelectBox> getRestrictShowFields(Integer fieldId, Integer resOptionId) {
        Assert.notNull(fieldId);
        Assert.notNull(resOptionId);
        String sql = " SELECT option_id,option_name  FROM sys_custom_field_restrict WHERE field_id = ? and res_option_id = ? ";

        return jdbcTemplate.query(sql,new Object[] { fieldId, resOptionId }, new RowMapper() {
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                SelectBox selb = new SelectBox();
                selb.setId(rs.getInt("option_id"));
                selb.setName(rs.getString("option_name"));
                return selb;
            }
        });
    }

    @Override
    public int setRestrictShowFields(Integer fieldId, List<SelectBox> list, Integer resFieldId, Integer resOptionId, String resOptionName) {
        Assert.notNull(fieldId);
        Assert.notNull(resOptionId);
        Assert.hasText(resOptionName);
        Assert.notNull(resFieldId);
        int [] lens = new int [0];
        String delSql = " DELETE FROM sys_custom_field_restrict WHERE res_option_id = ? and field_id = ? ";
        jdbcTemplate.update(delSql, new Object[] { resOptionId, fieldId });

        if (list != null && list.size() > 0) {
            String[] insSql = new String[list.size()];
            for (int i = 0; i < list.size(); i++) {
                SelectBox sb = list.get(i);
                insSql[i] = (" INSERT INTO sys_custom_field_restrict(field_id,option_id,option_name,res_field_id,res_option_id,res_option_name)  VALUES("
                        + fieldId + "," + sb.getId() + ",'" + sb.getName() + "'," + resFieldId + "," + resOptionId + ",'" + resOptionName + "')");
            }
            if (insSql.length > 0){
                lens = jdbcTemplate.batchUpdate(insSql);
            }
        }
        return lens.length;
    }

}
